﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;

/// <summary>
/// Summary description for DataProvider
/// </summary>
public class DataProvider
{
    private static SqlConnection _con = null;

	static DataProvider()
	{
		//Get connection string from web.conf or app.conf
        string strConnection = ConfigurationManager.ConnectionStrings["DauGia"].ConnectionString;
        _con = new SqlConnection(strConnection);
	}
    public static bool ExecuteNoneQuery(string strQuery, List<SqlParameter> sqlParameters)
    {
        bool result = false;
        try
        {
            _con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = strQuery;
            if (sqlParameters != null)
            {
                foreach (SqlParameter parameter in sqlParameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            int i = cmd.ExecuteNonQuery();
            if (i != -1)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            result = false;
            throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
        }
        finally
        {
            _con.Close();
        }
        return result;

    }
    public static DataTable ExecuteQuery(string strQuery, List<SqlParameter> sqlParameters)
    {
        DataTable resTable = new DataTable();
        try
        {
            _con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = strQuery;
            if (sqlParameters != null)
            {
                foreach (SqlParameter parameter in sqlParameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            adapter.Fill(resTable);


        }
        catch (Exception ex)
        {
            throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
        }
        finally
        {
            _con.Close();
        }
        return resTable;
    } 
    public static bool ExecuteNoneQuery(string strQuery)
    {
        bool result = false;
        try
        {
            _con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = strQuery;
            int i = cmd.ExecuteNonQuery();
            if (i == -1)
            {
                result = false;
            }
            result = true;
        }
        catch (Exception ex)
        {
            result = false;
            throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
        }
        finally {
            _con.Close();
        }
        return result;

    }
    public static Object ExecuteScalar(string strQuery)
    {
        Object resObject = new Object();

        try
        {
            _con.Open();
             SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = strQuery;
            resObject = (Object)cmd.ExecuteScalar();   
        
         
        }
        catch (Exception ex)
        {
            throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
        }
        finally
        {
            _con.Close();
        }

        return resObject;
    }
    public static DataTable ExecuteQuery(string strQuery)
    {
        DataTable resTable = new DataTable();

        try
        {
            _con.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(strQuery, _con);
            
            adapter.Fill(resTable);
        }
        catch (Exception ex)
        {
            throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
        }
        finally
        {
            _con.Close();
        }

        return resTable;
    }

    public static DataTable ExecuteStoreProc(string storeProcName, IList<string> arrParameterName, ArrayList arrParameterValue)
    {
        DataTable resTable = null;

        try
        {
            _con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = storeProcName;
            cmd.CommandType = CommandType.StoredProcedure;

            //Lay so parameter
            int N = arrParameterName.Count;
            for (int i = 0; i < N; i++)
            {
                SqlParameter sqlParam = new SqlParameter(arrParameterName[i], arrParameterValue[i]);
                cmd.Parameters.Add(sqlParam);
            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(resTable);
        }
        catch (Exception ex)
        {
            throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
        }
        finally {
            _con.Close();
        }

        return resTable;
    }
}
